ยท Create a new Package to Load Fact Table. Right Click on SSIS Packages ---- > Click New SSIS Package
ยท New Package will appear in Solution Explorer. Rename by Right click on Package name ---- > Click Rename --- > Type Fact_Mortgage ----- > Click Enter.
ยท Drag and Drop Execute SQL Task from Favorites in Control Flow
ยท Double Click on Execute SQL task. Execute SQL task Editor will pop up.
ยท In Connection Click Drop Down arrow ---- > Click New Connection
ยท Choose the Destination database (Mortgage Datamart) and Click Ok
ยท In SQL Statement ---- > Use the Code
TRUNCATE TABLE Fact_Mortgage
ยท ยท Just copy and Paste the code in SQL Statement ---- > Click Ok
ยท Drag and Drop Data flow Task from Favorites below Execute SQL Task and Provide link between
two just by Dragging the Green line from Execute SQL task and place it on Top of Data flow task
ยท Double Click on Data flow Task
ยท Another Editor will pop up. In that Choose the Database name (Colaberry Database) ----- > Click OK
ยท Click on the Drop down of Data access Mode ---- > Choose SQL Command
ยท Copy and Paste the code in SQL Command Text ---- > Click Columns Script to Copy:
SELECT Distinct [ContractID],[MortgageID],[PropertyID],[OwnerID],[PropertyTotalPrice],
[DownPayment],[BalanceDue],[AmountFinanced],[Term],[NumberOfPayments],[PaymentsRemaining],[APR],
[MonthlyPayment],[FinalPrice],[ProjectedProfit],[AmountRecovered],[BalanceRemaining],
[MaintenanceFee],[ARBalance],[ContractPointBalance]
FROM [ColaberryDatabase].[dbo].[MortgageStagingView]
ยท Drag and Drop Lookup and provide connection by dragging Blue arrow from OLE DB Source and
Place it on Top of Lookup
ยท Double Click on Lookup. Another Wizard will pop up.
ยท Click on Drop down of Specify how to handle rows with no matching entries ---- > Choose
Redirect row to no match output Click Connection
ยท Lookup Transformation editor will pop up. Click on Drop down of Connection Manager and
Choose destination Database (Mortgage Datamart) ---- > Click OK
ยท Choose Use results of an SQL Query ---- > Copy and Paste the Code
SELECT ContractID, ContractKey
FROM Dim_Contract
ยท Another Editor with Columns will Pop up. Drag ContractID from input columns and Drop it on
Lookup ContractID ----- > Click the Checkbox of ContractKey
ยท Change the Alias name to ContractKey_LKP
ยท Click OK
|
ยท Drag and Drop Lookup below look up 
ยท Provide connection by dragging Blue line and place it on Lookup. Another wizard will pop up.
Click on Drop down of Output and Choose Lookup Match Output
ยท Click OK
ยท Right Click on Lookup 1 ---- > Click Rename ---- > Type Lookup โ Dim_Mortgage
ยท Double click on Lookup โ Dim_Mortgage 
ยท Click on Drop down of Specify how to handle rows with no matching entries ----- > Choose Rows with
no match output
ยท Click Connection
ยท Another editor will pop up. Click on the Drop down of OLE DB Connection Manager ---- > Choose
Destination Database (Mortgage Datamart) ---- > Click OK
ยท Choose Use results of an SQL Query
ยท Copy and Paste the Code in Use results of an SQL Query
Script to paste:
SELECT MortgageID, MortgageKey
FROM Dim_Mortgage
ยท Another Editor will Pop up. Drag MortgageID from Input Column and Drag it on Lookup MortgageID
ยท Click the Checkbox of MortgageKey in Lookup Column
ยท Change Output Alias name to MortgageKey_LKP. Click OK
ยท Drag and Drop Lookup below Lookup โ Dim_Mortgage
ยท Provide connection by dragging Blue line and place it on Lookup. Another wizard will pop up.
ยท Click on Drop down of Output and Choose Lookup Match Output
ยท Click OK
ยท Right Click on Lookup 1 ---- > Click Rename ---- > Type Lookup โ Dim_Owner
ยท Double click on Lookup โ Dim_Owner
ยท Click on Drop down of Specify how to handle rows with no matching entries ----- > Choose
Rows with no match output
ยท Click Connection
ยท Another editor will pop up. Click on the Drop down of OLE DB Connection Manager ---- > Choose
Destination Database (Mortgage Datamart) ---- > Click OK
ยท Choose Use results of an SQL Query
ยท Copy and Paste the Code in Use results of an SQL Query
Script to paste:
SELECT OwnerID, OwnerKey
FROM Dim_Owner
ยท Another Editor will Pop up. Drag OwnerID from Input Column and Drag it on Lookup OwnerID
ยท Click the checkbox of OwnerKey in Lookup Column
ยท Change Output Alias name to OwnerKey_LKP. Click OK
ยท Drag and Drop Lookup below Lookup โ Dim_Owner
ยท Provide connection by dragging Blue line and place it on Lookup. Another wizard will pop up.
ยท Click on Drop down of Output and Choose Lookup Match Output
ยท Click OK
ยท Right Click on Lookup 1 ---- > Click Rename ---- > Type Lookup โ Dim_Property
ยท Double click on Lookup โ Dim_Property
ยท Click on Drop down of Specify how to handle rows with no matching entries ----- > Choose
Rows with no match output Click Connection
ยท Another editor will pop up. Click on the Drop down of OLE DB Connection Manager ---- > Choose
Destination Database (Mortgage Datamart) ---- > Click OK
ยท Choose Use results of an SQL Query
ยท Copy and Paste the Code in Use results of an SQL Query
Script to paste:
SELECT PropertyID, PropertyKey
FROM Dim_Property
ยท Another Editor will Pop up. Drag PropertyID from Input Column and Drag it on Lookup PropertyID
ยท Click the checkbox of PropertyKey in Lookup Column
ยท Change Output Alias name to PropertyKey _LKP. Click OK
ยท Drag and Drop OLE DB Destination from Other Destinations
ยท Provide connection by dragging Blue line and place it on Lookup. Another wizard will pop up.
ยท Click on Drop down of Output and Choose Lookup Match Output
ยท Click OK
ยท Double Click on OLE DB Destination 
ยท Click on the Drop Down of OLE DB Connection Manager ---- > Choose Destination Database
(Mortgage DataMart) ---- > Click OK
ยท Click on the Drop down of Name of the table or view ---- > Choose destination Table (Fact_Mortgage)
ยท Click on the Drop Down of ignore at Input Columns (Make sure in
ยท After matching Columns accordingly then click OK 
ยท Package is error free and ready for execution as shown below
To load the Fact table